﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;

namespace eVoting
{
    public class DBManager
    {
        DBConnectionDataContext context;

        public DBManager()
        {
            context = new DBConnectionDataContext();
        }

        

        //User(Student) Manager :

        public List<User> getAllUsers()
        {
            var usersQuery = from user in context.Users
                             select user;
            return usersQuery.ToList();
        }

        public List<User> getAllNonAdvisingStudents()
        {
            var usersQuery = from user in context.Users
                             where user.isAdvising == false
                                || user.isAdvising == null
                             select user;
            return usersQuery.ToList();
        }

        public User getUserById(int userID)
        {
            var userQuery = from user in context.Users
                            where user.ID == userID
                            select user;
            try { return userQuery.Single(); }
            catch { return null; }
        }

        public User getUserByGucId(string gucID)
        {
            var userQuery = from user in context.Users
                            where user.gucID == gucID
                            select user;
            try { return userQuery.Single(); }
            catch { return null; }
        }

        public User getUserByMail(string email)
        {
            var userQuery = from user in context.Users
                            where user.email == email
                            select user;
            try { return userQuery.Single(); }
            catch { return null; }
        }

        public List<User> getUsersByStudyGroup(int sgID)
        {
            var usersQuery = from user in context.Users
                             join studyGroup in context.StudyGroups
                             on user.studyGroup equals studyGroup.ID
                             where studyGroup.ID == sgID
                             select user;
            return usersQuery.ToList();
        }

        public List<User> getUsersByFaculty(int facultyID)
        {
            var usersQuery = from user in context.Users
                             join studyGroup in context.StudyGroups
                             on user.studyGroup equals studyGroup.ID
                             join faculty in context.Faculties
                             on studyGroup.faculty equals faculty.ID
                             where faculty.ID == facultyID
                             select user;
            return usersQuery.ToList();
        }


        public int getNumOfVotersByPollId(int pollID)
        {
            List<StudyGroup> SGs = getAssignedStudyGroupsByPollId(pollID);
            int num = 0;
            foreach (StudyGroup sg in SGs)
            {
                try { num += getUsersByStudyGroup(sg.ID).Count; }
                catch { }
            }
                
            return num;
        }

        public List<StudyGroup> getAssignedStudyGroupsByPollId(int pollID)
        {
            var sgQuery = from poll in context.Polls
                             where poll.ID == pollID
                             join pollSG in context.PollStudyGroups
                             on poll.ID equals pollSG.poll
                             select pollSG.StudyGroup1;
            return sgQuery.ToList();
        }

        public List<Poll> getAssignedPollsByUserID(int userID)
        {
            //var pollsQuery = from poll in context.Polls
            //                 join pollSG in context.PollStudyGroups
            //                 on poll.ID equals pollSG.poll
            //                 where pollSG.studyGroup == getUserById(userID).studyGroup
            //                 select poll;
            //return pollsQuery.ToList();


            var pollsQuery = from user in context.Users
                              join pollSG in context.PollStudyGroups
                              on user.studyGroup equals pollSG.studyGroup
                              join poll in context.Polls
                              on pollSG.poll equals poll.ID
                              where userID == user.ID
                              select poll;
            return pollsQuery.ToList();
        }

        public bool createNewUser(string gucID, string firstName, string middleName, string lastName, string email, string password, int studyGroupID, bool? isAdvising)
        {
            User newUser = new User();
            newUser.gucID = gucID;
            newUser.firstName = firstName;
            newUser.middleName = middleName;
            newUser.lastName = lastName;
            newUser.email = email;
            newUser.password = password;
            newUser.studyGroup = studyGroupID;
            newUser.isAdvising = isAdvising;

            context.Users.InsertOnSubmit(newUser);
            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        public void confirmUser(int userID)
        {
            try
            {
                User user = getUserById(userID);
                user.isConfirmed = true;
                context.SubmitChanges();
            }
            catch { }
        }

        public void banUserFromVoting(int userID)
        {
            try
            {
                User user = getUserById(userID);
                user.isBanned = true;
                context.SubmitChanges();
            }
            catch { }
        }

        public void markUserAsAdvising(int userID)
        {
            try
            {
                User user = getUserById(userID);
                user.isAdvising = true;
                context.SubmitChanges();
            }
            catch { }
        }

        //*******************


        //Candidate Manager :

        public List<Candidate> getCandidatesByPoll(int pollID)
        {
            var candidatesQuery = from candidate in context.Candidates
                                  where candidate.poll == pollID
                                  select candidate;
            return candidatesQuery.ToList();
        }

        public Candidate createNewCandidate(string firstName, string middleName, string lastName, int pollID)
        {
            Candidate myCandidate = new Candidate();
            myCandidate.firstName = firstName;
            myCandidate.middleName = middleName;
            myCandidate.lastName = lastName;
            myCandidate.Poll1 = getPollById(pollID);
            myCandidate.voteCount = 0;

            context.Candidates.InsertOnSubmit(myCandidate);
            try { context.SubmitChanges(); return myCandidate; }
            catch { return null; }
        }


        //**************


        //StudyGroup Manager :

        public StudyGroup getStudyGroup(int facultyID, int academicYear)
        {
            var sgQuery = from sg in context.StudyGroups
                          where sg.faculty == facultyID
                          where sg.year == academicYear
                          select sg;

            try { return sgQuery.Single(); }
            catch { return null; }
        }

        public List<StudyGroup> getAllStudyGroups()
        {
            var sgsQuery = from sg in context.StudyGroups
                           select sg;
            return sgsQuery.ToList();
        }

        public void updateStudyGroupForAllNonAdvisingStudents(int currentYear)
        {
            List<User> nonAdvisingStudents = getAllNonAdvisingStudents();

            foreach (User student in nonAdvisingStudents)
            {
                int academicYear = getAcademicYear(student.gucID, currentYear);
                StudyGroup newSG = getStudyGroup(student.StudyGroup1.faculty, academicYear);

                student.StudyGroup1 = newSG;

                context.SubmitChanges();
            }
        }

        //*********************

        // Poll Manager :

        public Poll createNewPoll(string name, string description, DateTime? startDate, DateTime? endDate)
        {
            Poll myPoll = new Poll();
            myPoll.name = name;
            myPoll.description = description;
            myPoll.startDate = startDate;
            myPoll.endDate = endDate;

            context.Polls.InsertOnSubmit(myPoll);
            try { context.SubmitChanges(); return myPoll; }
            catch { return null; }
        }

        public Poll getPollById(int pollID)
        {
            var pollQuery = from poll in context.Polls
                            where poll.ID == pollID
                            select poll;
            try { return pollQuery.Single(); }
            catch { return null; }
        }

        public List<Poll> getAllPolls()
        {
            var pollQuery = from poll in context.Polls
                           select poll;
            return pollQuery.ToList();
        }

        public bool assignPollToStudyGroup(int pollID, int sgID)
        {
            PollStudyGroup ps = new PollStudyGroup();
            ps.poll = pollID;
            ps.studyGroup = sgID;

            context.PollStudyGroups.InsertOnSubmit(ps);
            try { context.SubmitChanges(); return true; }
            catch { return false; }

        }

        public bool assignPollToStudyGroup(Poll poll, StudyGroup studyGroup)
        {
            PollStudyGroup ps = new PollStudyGroup();
            ps.Poll1 = poll;
            ps.StudyGroup1 = studyGroup;

            context.PollStudyGroups.InsertOnSubmit(ps);
            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        public bool assignPollToStudyGroups(int pollID, int[] sgIDs)
        {
            foreach (int sgID in sgIDs)
            {
                PollStudyGroup ps = new PollStudyGroup();
                ps.poll = pollID;
                ps.studyGroup = sgID;

                context.PollStudyGroups.InsertOnSubmit(ps);
            }

            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        public bool assignPollToStudyGroups(Poll poll, List<StudyGroup> studyGroups)
        {
            foreach (StudyGroup studyGroup in studyGroups)
            {
                PollStudyGroup ps = new PollStudyGroup();
                ps.Poll1 = poll;
                ps.StudyGroup1 = studyGroup;

                context.PollStudyGroups.InsertOnSubmit(ps);
            }

            try { context.SubmitChanges(); return true; }
            catch { return false; }
        }

        //*********************

        public List<Faculty> getAllFaculties()
        {
            var facQuery = from faculty in context.Faculties
                             select faculty;
            return facQuery.ToList();
        }


        public Faculty getFacultyById(int facultyID)
        {
            var facQuery = from faculty in context.Faculties
                            where faculty.ID == facultyID
                            select faculty;
            try { return facQuery.Single(); }
            catch { return null; }
        }

        public Faculty getFacultyByAbb(string abb)
        {
            var facQuery = from faculty in context.Faculties
                           where faculty.abbreviation == abb
                           select faculty;
            try { return facQuery.Single(); }
            catch { return null; }
        }

        public Faculty getFacultyByName(string facultyName)
        {
            var facQuery = from faculty in context.Faculties
                           where faculty.name == facultyName
                           select faculty;
            try { return facQuery.Single(); }
            catch { return null; }
        }

        public int getFacultyIdByName(string facultyName)
        {
            var facQuery = from faculty in context.Faculties
                           where faculty.name == facultyName
                           select faculty.ID;
            try { return facQuery.Single(); }
            catch { return 0; }
        }



        //public DateTime getPWDate(int userID)
        //{
        //    User user = getUserById(userID);

        //    return user.;
        //}


        //public void changePW(int userID, string pw)
        //{
         //   User user = getUserById(userID);

//            user.password = pw;

  //          user.pwDate = DateTime.Now;

    //        context.SubmitChanges();
      //  }

        public List<Candidate> getPollResults(int pollId)
        {
            var query = from poll in context.Polls
                        join candidates in context.Candidates
                        on poll.ID equals candidates.poll
                        where poll.ID == pollId
                        select candidates;
            return query.ToList();

        }

        // Helper Methods :

        private int getAcademicYear(string gucID, int currentYear)
        {
            int beforeDash = Int32.Parse(gucID.Split('-')[0]);
            int classNum = (int)(beforeDash + 2) / 3;
            int academicYear = currentYear - classNum - 2001;
            return academicYear;
        }
    }
}
